package com.hw.dagl.service.impl;

import com.hw.dagl.model.Vehicle;
import com.hw.dagl.service.VehicleService;
import com.hw.dao.utils.EntityTools;
import com.hw.dao.utils.OraclePageDaoBase;
import com.hw.dao.utils.Page;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


@Service
public class VehiclexxServiceImpl implements VehicleService {

    @Resource
    private OraclePageDaoBase oraclePageDaoBase;
    /**
     * 查询机动车基本信息信息
     */
    @Override
    public Page<Vehicle> findJdcxx(Page<Vehicle> pages,Vehicle info,String sltbz) {
        StringBuffer sql = new StringBuffer(" select t.xh,t.hpzl,t.hphm,t.clpp1,t.clxh, " +
                " t.zzcmc,t.cllx,t.csys,t.syxz,t.syr,t.sfzmhm,t.clsbdh from vehicle t " +
//                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl " +//机动车号牌种类代码
//                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1004' and t.xtlb='00') lx on lx.dmz=t.cllx " +//机动车辆类型代码
//                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1008' and t.xtlb='00') ys on ys.dmz=t.csys " +//机动车车身颜色基本色调代码
//                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1003' and t.xtlb='00') sy on sy.dmz=t.syxz " +//机动车使用性质
                " where 1=1 ");
        boolean isJqSlt = "1".equals(sltbz);//是否是精确查询
        List<Object> args = new ArrayList<Object>();
        if(StringUtils.isNotEmpty(info.getHphm())){
            if(isJqSlt){
                sql.append(" AND t.hphm = ? ");
            }else{
                sql.append(" AND instr(t.hphm,?) > 0 ");
            }
            args.add(info.getHphm());
        }
        if(StringUtils.isNotEmpty(info.getCllx())){
            sql.append(" AND t.cllx = ? ");
            args.add(info.getCllx());
        }
        if(StringUtils.isNotEmpty(info.getSfzmhm())){
            if(isJqSlt){
                sql.append(" AND t.sfzmhm = ? ");
            }else{
                sql.append(" AND instr(t.sfzmhm,?) > 0 ");
            }
            args.add(info.getSfzmhm());
        }
        if(StringUtils.isNotEmpty(info.getHpzl())){
            sql.append(" AND t.hpzl = ? ");
            args.add(info.getHpzl());
        }
        if(StringUtils.isNotEmpty(info.getStr())){
            sql.append(" AND to_char(t.ccdjrq,'yyyy-mm-dd') = ? ");
            args.add(info.getStr());
        }

        if(args.size() <= 0 ){
            return pages;
        }

       Map m = oraclePageDaoBase.getPageSQLAndTotal(sql.toString(), pages, args.toArray());
        pages.setTotal(Long.parseLong(m.get("total").toString()));//总记录数

        String pageSQL = " select t2.xh,hp.dmsm1 as hpzl,t2.hphm,t2.clpp1,t2.clxh, " +
                " t2.zzcmc,lx.dmsm1 as cllx,ys.dmsm1 as csys,sy.dmsm1 as syxz,t2.syr,t2.sfzmhm,t2.clsbdh from ("+m.get("pageSQL").toString()+") t2 " +
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t2.hpzl " +//机动车号牌种类代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1004' and t.xtlb='00') lx on lx.dmz=t2.cllx " +//机动车辆类型代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1008' and t.xtlb='00') ys on ys.dmz=t2.csys " +//机动车车身颜色基本色调代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1003' and t.xtlb='00') sy on sy.dmz=t2.syxz " +//机动车使用性质
                " where 1=1 ";

        List<Vehicle> listRows = oraclePageDaoBase.query(pageSQL,args.toArray(),new RowMapper<Vehicle>(){
            private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
            @Override
            public Vehicle mapRow(ResultSet rs, int arg1)throws SQLException {
                Vehicle jdcxx = new Vehicle();
                et.setColumnsValueByResultSet(rs, jdcxx);
                return jdcxx;
            }
        });

        pages.setRows(listRows);//数据
        return pages;
    }
    /**
     * 根据序号查询机动车信息
     */
    @Override
    public Vehicle findJdcxxByXh(Vehicle info) {
        String sql = " select t.*,hp.dmsm1 hpzlmc, lx.dmsm1 as cllxmc1,ys.dmsm1 as csysmc,sy.dmsm1 as syxzmc,sfz.dmsm1 sfzmmcmc,"
        		+ " decode(t.syq,'2','个人','1','单位','个人') syqmc from vehicle t " +
        	    " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1007' and t.xtlb='00') hp on hp.dmz=t.hpzl " +//机动车号牌种类代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1004' and t.xtlb='00') lx on lx.dmz=t.cllx " +//机动车辆类型代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1008' and t.xtlb='00') ys on ys.dmz=t.csys " +//机动车车身颜色基本色调代码
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='1003' and t.xtlb='00') sy on sy.dmz=t.syxz " +//机动车使用性质
                " left join (select t.* from Frm_Code t where 1=1 and t.dmlb='2019' and t.xtlb='00') sfz on sfz.dmz=t.sfzmmc " +//身份证名称
              	" where t.xh=? ";
        List<Vehicle> list = new ArrayList<Vehicle>();
        if(info != null){
            list= oraclePageDaoBase.query(sql, new Object[]{info.getXh()},new RowMapper<Vehicle>(){
                private EntityTools<Vehicle> et = new EntityTools<Vehicle>();
                @Override
                public Vehicle mapRow(ResultSet rs, int index)
                        throws SQLException {
                    Vehicle info = new Vehicle();
                    et.setColumnsValueByResultSet(rs, info);
                    return info;
                }
            });
        }
        return list.size() > 0  ? list.get(0) : null;
    }
}
